PostgreSQL 基础与 MySQL 对照

PostgreSQL 系统讲解第一篇。我 MySQL 已经过了一遍，所以这组用对照式学习——每讲一个点都问"MySQL 里我怎么做、PG 里有什么不一样"。PG 在国外互联网公司和 AI 应用里越来越常见（pgvector 就是它的扩展），值得系统学一遍。

为什么 PG 值得系统学

  • 功能天花板高：JSONB、数组、窗口函数、全文检索、自定义类型全是原生能力，很多 MySQL 要插件或绕路的事 PG 内置
  • 标准兼容与严谨：SQL 标准执行得更彻底，事务里 DDL 都能回滚
  • 生态强：pgvector（向量检索）、PostGIS（地理）、TimescaleDB（时序）——本数据库组"03-向量数据库"篇未来实操大概率落在它上面
  • MySQL 兼容不了的场景：复杂分析查询、GIS、严格的数据完整性要求

安装与连接

brew install postgresql@16          # macOS；Ubuntu 用 apt install postgresql
brew services start postgresql@16   # 启动服务

psql -U zhouwei5 -d postgres        # 本地进入交互终端；默认超级用户=当前系统用户
createdb mydb                       # 建库
psql "postgresql://user:pass@host:5432/dbname"   # 标准 URI 连接

💡 第一个坑：PG 的"用户"是集群级、库是实例里的一份独立数据空间——psql 不带 -d 时默认连和用户同名的库，不存在就报错，先 createdb 或连 postgres 库。

psql 元命令速记

psql 的反斜杠命令相当于 MySQL 的 show/desc：

命令 作用 MySQL 对应
\l 列出所有数据库 SHOW DATABASES
\c dbname 切换数据库 USE dbname
\dt 列出当前 schema 的表 SHOW TABLES
\d 表名 表结构（列/索引/约束） DESC 表名 + SHOW CREATE TABLE
\di / \dv / \ds 列索引 / 视图 / 序列 —
\x 扩展显示（竖排），宽表救星 \G
\timing 显示每条 SQL 耗时 —
\q 退出 exit

与 MySQL 的核心差异总表

维度 MySQL PostgreSQL
标识符大小写 表名大小写敏感性看平台 未加引号的标识符一律折叠成小写
层级 database ≈ schema database → schema（一个库内多个命名空间）→ table
自增 AUTO_INCREMENT SERIAL / GENERATED ALWAYS AS IDENTITY（推荐后者，SQL 标准）
引号语义 双引号可用于字符串 双引号=标识符，字符串只用单引号
DDL 不支持事务回滚 DDL 在事务里可回滚（改错表结构 ROLLBACK 即可）
模糊查询 LIKE 大小写看排序规则 ILIKE 忽略大小写（还有 ~ 正则操作符）
元信息 information_schema 为主 information_schema + \d 系列 + pg_catalog
日期函数 DATE_ADD、DATE_SUB INTERVAL '7 days' 直接参与运算

💡 最容易撞的坑就是引号和大小写：SELECT "Name" FROM users 在 PG 里是找叫 Name（大写）的列——而建表时没加引号的话它早被折叠成 name 了，于是报"列不存在"。MySQL 转过来的人几乎都撞过一次。

基本对象操作对照

CREATE DATABASE shop;                      -- 与 MySQL 同
CREATE TABLE users (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      varchar(64) NOT NULL,
    email     varchar(128) UNIQUE,
    tags      text[],                        -- 数组类型！MySQL 没有的
    attrs     jsonb,                         -- JSONB，见 02 篇
    created_at timestamptz DEFAULT now()
);
INSERT INTO users (name, email) VALUES ('张三', 'z@ex.com') RETURNING id;  -- INSERT 也能 RETURNING 取回自增 id

我的记忆锚点：PG 里" 能力都是内建的"——数组、JSONB、全文检索、地理、向量（扩展）；MySQL 是"够用就好靠生态补"。学 PG 的过程就是把 MySQL 里"绕路"的经验重新对一遍答案。

⬅️ 18-事务与并发控制 🏠 00-数据库 ➡️ 02-PostgreSQL 数据类型与表设计